package org.tea.inventory.db.util.excel;

import org.tea.inventory.db.domain.AlumniWorkerWelfare;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.ss.usermodel.*;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

public class ExcelParse {
    private static Log log = LogFactory.getLog(ExcelParse.class);

    /**
     * 解析员工福利模板
     * @param filePath
     * @return
     * @throws Exception
     */
    public static List<AlumniWorkerWelfare> reader(String filePath) throws Exception{
        List<AlumniWorkerWelfare> listWelfare = new ArrayList<AlumniWorkerWelfare>();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        InputStream in = new FileInputStream(new File(filePath));

        Workbook wb = WorkbookFactory.create(filePath);

        Sheet sheet = wb.getSheetAt(0);
        int rowTotal = sheet.getPhysicalNumberOfRows();
        if(rowTotal <= 2){
            log.info("路径为：" + filePath + " 的Excel文件不包含任何可读取的员工信息");
        }

        for(int r = 2; r < rowTotal; r++){
            Row row = sheet.getRow(r);
            if(row == null){
                continue;
            }

            AlumniWorkerWelfare welfare = new AlumniWorkerWelfare();

            if(row.getLastCellNum() < 5){
                throw new Exception("excel格式错误,员工号、员工名称、福利名称、兑换码、密码...为必填项");
            }

            //员工号
            Cell cell = row.getCell(0);
            Object obj = ExcelReader.getValue(cell);
            welfare.setEmployeno(obj == null ? "" : obj.toString().trim());

            //员工名称
            cell = row.getCell(1);
            obj = ExcelReader.getValue(cell);
            welfare.setWorkerName(obj == null ? "" : obj.toString().trim());

            //福利名称
            cell = row.getCell(2);
            obj = ExcelReader.getValue(cell);
            welfare.setWelfareName(obj == null ? "" : obj.toString().trim());

            //兑换码
            cell = row.getCell(3);
            obj = ExcelReader.getValue(cell);
            welfare.setRedemptionCode(obj == null ? "" : obj.toString().trim());

            //说明
            cell = row.getCell(4);
            obj = ExcelReader.getValue(cell);
            welfare.setContent(obj == null ? "" : obj.toString().trim());



            //开始时间
            cell = row.getCell(5);
            obj = ExcelReader.getValue(cell);
            welfare.setStartTime(obj == null ? null : sdf.parse((String)obj));

            //结束时间
            cell = row.getCell(6);
            obj = ExcelReader.getValue(cell);
            welfare.setEndTime(obj == null ? null : sdf.parse((String)obj));

            listWelfare.add(welfare);

        }

        in.close();

        return listWelfare;
    }

    //判断对象是否可以转数字
    public static boolean isNumber (Object obj) {
        if (obj instanceof Number) {
            return true;
        } else if (obj instanceof String) {
            try {
                Double.parseDouble((String) obj);
                return true;
            } catch (Exception e) {
                return false;
            }
        }
        return false;
    }
}
